
package cn.hhchat.kit.office;

import cn.hhchat.kit.annotation.ExcelColumn;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * poi 读取excel 支持2003 --2007 及以上文件
 *
 * author sunny
 * version V 2.0
 * CreateTime 2013-11-19
 * update author hh23485
 * updateTime 2017-7-15
 */
@Slf4j
public class ExcelUtils {



    /**
     * 合并方法，读取excel文件
     * 根据文件名自动识别读取方式
     * 支持97-2013格式的excel文档
     *
     * @return 返回列表内容格式：
     *  每一行数据都是以对应列的表头为key 内容为value 比如 excel表格为：
     * ===============
     *  A | B | C | D
     * ===|===|===|===
     *  1 | 2 | 3 | 4
     * ---|---|---|---
     *  a | b | c | d
     * ---------------
     * 返回值 map：
     *   map1:   A:1 B:2 C:3 D:4
     *   map2:   A:a B:b C:d D:d
     * @throws IOException 读取异常
     */
    public static ExcelMatrix readExcel(File excel) throws Exception{
        //准备返回值列表
        String fileName = excel.getName();
        String ExtensionName=getExtensionName(fileName);
        if(ExtensionName.equalsIgnoreCase("xls")){
            return readExcel2003(excel);
        }else if(ExtensionName.equalsIgnoreCase("xlsx")) {
            return readExcel2007(excel);
        }
        return null;
    }

    /**
     * 读取97-2003格式
     * @throws IOException
     */
    @SuppressWarnings("rawtypes")
    public static ExcelMatrix readExcel2003(File excel) throws IOException{
        //返回结果集

        ExcelMatrix matrix = new ExcelMatrix();
        FileInputStream fis=null;
        try {
            fis=new FileInputStream(excel);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);  // 创建对Excel工作簿文件的引用
            HSSFSheet sheet = workbook.getSheetAt(0);   // 在Excel文档中，第一张工作表的缺省索引是0
            int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数­
            List<String> keys = new LinkedList<>();
            int cells=0;
            // 遍历行­（第1行  表头） 准备Map里的key
            HSSFRow firstRow = sheet.getRow(0);
            if (firstRow != null) {
                // 获取到Excel文件中的所有的列
                cells = firstRow.getPhysicalNumberOfCells();
                // 遍历列
                for (int j = 0; j < cells; j++) {
                    // 获取到列的值­
                    try {
                        HSSFCell cell = firstRow.getCell(j);
                        String cellValue = getCellValue(cell);
                        keys.add(cellValue);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            matrix.setHeader(keys);
            LinkedList<List<String>> body = new LinkedList<>();
            // 遍历行­（从第二行开始）
            for (int i = 1; i < rows; i++) {
                // 读取左上端单元格(从第二行开始)
                HSSFRow row = sheet.getRow(i);
                // 行不为空
                if (row != null) {
                    //准备当前行 所储存值的map
                    List<String> val = new LinkedList<>();

                    boolean isValidRow = false;

                    // 遍历列
                    for (int j = 0; j < cells; j++) {
                        // 获取到列的值­
                        try {
                            HSSFCell cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            val.add(cellValue);
                            if(!isValidRow && cellValue!=null && cellValue.trim().length()>0){
                                isValidRow = true;
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    //第I行所有的列数据读取完毕，放入valuelist
                    if(isValidRow){
                        body.add(val);
                    }
                }
            }
            matrix.setBody(body);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            fis.close();
        }
        return matrix;
    }
    /**
     * 读取2007-2013格式
     * @return 读取的结果
     * @throws IOException 文件读取异常
     */
    @SuppressWarnings("rawtypes")
    public static ExcelMatrix readExcel2007(File excel) throws IOException{
        ExcelMatrix matrix = new ExcelMatrix();
        FileInputStream fis =null;
        try {
            fis =new FileInputStream(excel);
            XSSFWorkbook xwb = new XSSFWorkbook(fis);   // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            XSSFSheet sheet = xwb.getSheetAt(0);            // 读取第一章表格内容
            // 定义 row、cell
            XSSFRow row;
            // 循环输出表格中的第一行内容   表头
            LinkedList<String> keys = new LinkedList<>();
            row = sheet.getRow(0);
            if(row !=null){
                //System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
                //System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int j = row.getFirstCellNum(); j < physicalNumberOfCells; j++) {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    if(row.getCell(j)!=null){
                        if(!row.getCell(j).toString().isEmpty()){

                            keys.add(row.getCell(j).toString());
                        }
                    }else{
                        keys.add(null);
                    }
                }
            }
            matrix.setHeader(keys);

            LinkedList<List<String>> body = new LinkedList<>();
            // 循环输出表格中的从第二行开始内容
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    boolean isValidRow = false;
                    LinkedList<String>  val = new LinkedList<>();
                    for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
                        XSSFCell cell = row.getCell(j);
                        if (cell != null) {
                            String cellValue = null;
                            if(cell.getCellTypeEnum()== CellType.NUMERIC){
                                if(DateUtil.isCellDateFormatted(cell)){
                                    cellValue = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
                                }
                                else{
                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                }
                            }
                            else{
                                cellValue = cell.toString();
                            }
                            if(cellValue!=null&&cellValue.trim().length()<=0){
                                cellValue=null;
                            }
                            val.add(cellValue);
                            if(!isValidRow && cellValue!= null && cellValue.trim().length()>0){
                                isValidRow = true;
                            }
                        }
                    }
                    // 第I行所有的列数据读取完毕，放入 valueList
                    if (isValidRow) {
                        body.add(val);
                    }
                }
            }
            matrix.setBody(body);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            fis.close();
        }

        return matrix;
    }

    /**
     * 文件操作 获取文件扩展名
     *
     * @param filename
     *            文件名称包含扩展名
     * @return 文件扩展名
     */
    public static String getExtensionName(String filename) {
        if ((filename != null) && (filename.length() > 0)) {
            int dot = filename.lastIndexOf('.');
            if ((dot > -1) && (dot < (filename.length() - 1))) {
                return filename.substring(dot + 1);
            }
        }
        return filename;
    }

    /** -----------上传文件,工具方法--------- */
    private static final int BUFFER_SIZE = 2 * 1024;

    /**
     *
     * @param src
     *            源文件
     * @param dst
     *            目标位置
     */
    private static void copy(File src, File dst) {
        InputStream in = null;
        OutputStream out = null;
        try {
            in = new BufferedInputStream(new FileInputStream(src), BUFFER_SIZE);
            out = new BufferedOutputStream(new FileOutputStream(dst),
                    BUFFER_SIZE);
            byte[] buffer = new byte[BUFFER_SIZE];
            int len = 0;
            while ((len = in.read(buffer)) > 0) {
                out.write(buffer, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != in) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private static String getCellValue(HSSFCell cell) {
        DecimalFormat df = new DecimalFormat("#");
        String cellValue=null;
        if (cell == null)
            return null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    break;
                }
                cellValue=df.format(cell.getNumericCellValue());
                break;
            case STRING:
                cellValue=String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                cellValue=String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
                cellValue=null;
                break;
            case BOOLEAN:
                cellValue=String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                cellValue=String.valueOf(cell.getErrorCellValue());
                break;
        }
        if(cellValue!=null&&cellValue.trim().length()<=0){
            cellValue=null;
        }
        return cellValue;
    }

    public static<T> File generateExcel(List<T> data,List<String> headers,  String path) throws IOException {
        //如果只有data为空，就只写入header
        File file = new File(path);
        File dic = new File(file.getParent());
        //创建文件夹
        if (!dic.exists()) {
            if(!dic.mkdirs()){
                throw new FileNotFoundException("文件夹创建失败");
            }
        }
        //创建文件
        Boolean createResult = file.createNewFile();
//        //如果创建不成功
//        if (!createResult) {
//            throw new FileNotFoundException("文件创建失败");
//        }
        //如果是空，则不存储
        XSSFWorkbook hw = new XSSFWorkbook();
        if (data == null || data.size() == 0) {
            return null;
        }
        //开始写入数据
        Field[] declaredFields = data.get(0).getClass().getDeclaredFields();
        Arrays.stream(declaredFields).forEach(f -> f.setAccessible(true));
        List<Field> fields = new ArrayList<>(headers.size());
        for (Field f : declaredFields) {
            ExcelColumn excelColumn = f.getAnnotation(ExcelColumn.class);
            if (excelColumn != null && headers.contains(excelColumn.value())) {
                fields.add(f);
            }
        }

        //写入表格头
        createExcel(hw, fields);
        //写入第一张表
        XSSFSheet sheet = hw.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        //写入数据
        for(int i=0;i<data.size();i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < fields.size(); j++) {
                cell = row.createCell(j);
                try {
                    Object obj = fields.get(j).get(data.get(i));
                    if (obj != null) {
                        cell.setCellValue(obj.toString());
                    }
                } catch (IllegalAccessException e) {
                    log.warn("ignore illegal value");
                }
            }
        }
        hw.write(new FileOutputStream(file));
        return file;
    }

    private static XSSFWorkbook createExcel(XSSFWorkbook hw, List<Field> fields) {
        XSSFSheet sheet = hw.createSheet();
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell;
        for(int i=0;i<fields.size();i++) {
            //获取列名
            ExcelColumn cls = fields.get(i).getAnnotation(ExcelColumn.class);
            String columnName = cls.value();
            cell = row.createCell(i);
            cell.setCellValue(columnName);
        }
        return hw;
    }

    public static List<String> getHeader(Class cls) {
        List<String> headers = new ArrayList<>();
        Field[] declaredFields = cls.getDeclaredFields();
        Arrays.stream(declaredFields).forEach(f -> f.setAccessible(true));
        for(Field f:declaredFields) {
            //获取列名
            ExcelColumn col = f.getAnnotation(ExcelColumn.class);
            if (col == null) {
                continue;
            }
            String columnName = col.value();
            if (StringUtils.isEmpty(columnName)) {
                continue;
            }
            headers.add(columnName);
        }
        return headers;
    }

    //操作行
    //操作列
    //使用Matrix生成

}